/*
 * Reference: Web Apps code provided by Prof Charlie Garrod
 * can be used for educational purposes
 * */
package messagepasser;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import document.DocInfo;
import document.DocPermissions;

public class DocListDAOSQL {
	private List<Connection> connectionPool = new ArrayList<Connection>();

	private String jdbcDriver;
	private String jdbcURL;
	private String tableName;

	public DocListDAOSQL(String jdbcDriver, String jdbcURL, String tableName) {
		this.jdbcDriver = jdbcDriver;
		this.jdbcURL    = jdbcURL;
		this.tableName  = tableName;

		createTable();
	}

	private synchronized Connection getConnection() {
		if (connectionPool.size() > 0) {
			return connectionPool.remove(connectionPool.size()-1);
		}

		try {
			Class.forName(jdbcDriver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new AssertionError(e);
		}

		try {
			return DriverManager.getConnection(jdbcURL);
		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}

	private synchronized void releaseConnection(Connection con) {
		connectionPool.add(con);
	}

	private void createTable() {
		Connection con = getConnection();
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(
					"create table "+tableName+
					" (username varchar(20) NOT NULL," +
					" updates varchar(30)," +
					" id MEDIUMINT auto_increment, primary key(id)," +
			" foreign key(username) references aj1_profile(username))");
			stmt.close();
			releaseConnection(con);
		} catch (SQLException e) {
			try { con.close(); } catch (SQLException e2) { /* ignore */ }
		//	System.out.println("Exception creating table: "+e);
		//	System.out.println("...assume it already exists");
		}
	}


	/*	
	public void setupdate(String update, String user){
try {

			Connection con = getConnection();
        	con.setAutoCommit(false);

        	int num = 0;
        	Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("select id from "+tableName+" order by id");
            while (rs.next()) {
            	num = (rs.getInt("id"));
            }
            rs.close();
            stmt.close();

            PreparedStatement pstmt = con.prepareStatement("insert into "+tableName+" values (?,?,?)");
 //           Date d1 = new Date();
            pstmt.setString(1, user);
            pstmt.setString(2, update);
            pstmt.setInt(3, num+1);


            pstmt.executeUpdate();
            pstmt.close();

            con.commit();
            con.setAutoCommit(true);

            releaseConnection(con);

        } catch (SQLException e) {
            throw new AssertionError(e);
        }
	}
	 */
	public void initialize(){
		try {

			Connection con = getConnection();
			con.setAutoCommit(false);
			int num=0;
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select count(username) as cnt from "+tableName);
			while (rs.next()) {
				num = (rs.getInt("cnt"));
			}
			rs.close();
			stmt.close();
			if(num == 0)
			{
				PreparedStatement pstmt = con.prepareStatement("insert into "+tableName+" values (?,?,?)");

			}
			con.commit();
			con.setAutoCommit(true);

			releaseConnection(con);

		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}



	//To get all the doc ids for which the user is owner
	public synchronized Integer[] OwnerDocids(int ownerid) {
		try {
			Connection con = getConnection();

			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select docid from document where ownerid = "+ownerid);

			List<Integer> list = new ArrayList<Integer>();
			while (rs.next()) {
				list.add(Integer.parseInt(rs.getString("docid")));
			}
			rs.close();
			stmt.close();
			releaseConnection(con);

			return list.toArray(new Integer[list.size()]);

		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}
	
	//To get information about all the documents for which user is given some permission
	public synchronized List<DocInfo> getDocsInfo(int userid) {
		List<DocInfo> docInfo = null;
		try {
			Connection con = getConnection();
			docInfo = new ArrayList<DocInfo>();
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select docid from permissions where userid = "+userid);
			List <Integer> docIds = new ArrayList <Integer>();
			while (rs.next()) {
				docIds.add(rs.getInt("docid"));
			}
			
			for (Integer i : docIds) {
				rs = stmt.executeQuery("select * from document where docid = " + i.intValue());
				while (rs.next()) {
						docInfo.add(new DocInfo(i.intValue(), rs.getString("docname"), rs.getString("description"), rs.getString("lastedit"), rs.getInt("ownerid")));
				}
			}
			rs.close();
			stmt.close();
			releaseConnection(con);

			

		} catch (SQLException e) {
			throw new AssertionError(e);
		}
		return docInfo;
	}
	
	//Method to get all the permissions of a specific user
	public synchronized  DocPermissions[] getDocPermissions(int ownerid) {
		try {
			Connection con = getConnection();

			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select docid from permissions where ownerid = "+ownerid+" AND docid NOT IN(Select docid from document where ownerid = \'"+ownerid+"\')");
			//ResultSet rs2 = stmt.executeQuery("select docid from permissions where ownerid = "+ownerid+" AND docid NOT IN(Select docid from document where ownerid = \'"+ownerid+"\')");
			//Getting all doc ids for which user is owner and for which he has entry in permissions table
			
			List<Integer> docids = new ArrayList<Integer>();
			while (rs.next()) {
				docids.add(Integer.parseInt(rs.getString("docid")));
			}
			
			String accesstype = null;
			DocPermissions[] docPermissions = null;

			for(int i=0;i<docids.size();i++){
				rs = stmt.executeQuery("select accesstype from permissions where docid = "+docids.get(i));
				while (rs.next()) {
					accesstype = rs.getString("accesstype");
				}
				docPermissions[i] = new DocPermissions(docids.get(i), ownerid, accesstype);
			}

			rs.close();
			stmt.close();
			releaseConnection(con);

			return docPermissions;

		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}


	//Insert new Document 
	public int insertDoc(String name, String desc, String lastedit, int ownerid, String loc){
		try {

			Connection con = getConnection();

			int id = 0;
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select count(docname) from document where docname = \'"+name+"\' and ownerid = "+ownerid);
			int numdocs = 0;
			while (rs.next()) {
				numdocs = rs.getInt("count(docname)");
			}
			if(numdocs>0){
				System.out.println("Document with same name already exists for this user");
				return 0;
			}


			rs = stmt.executeQuery("select max(docid) as docid from document");


			while (rs.next()) {
				id = rs.getInt("docid");
			}
			id++;

			if(loc==null){
				System.out.println("Location Not Available");
				return 0;
			}
			Writer out = null;
			try {
				System.out.println("in DocListDAO SQL loc = "+loc);
				out = new BufferedWriter(new FileWriter(loc));
			} catch (IOException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			try{
				try {
					out.write("");
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}finally{
				try {
					out.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}

			con.setAutoCommit(false);
			stmt = con.createStatement();
			stmt.close();
			PreparedStatement pstmt = con.prepareStatement("insert into document values (?,?,?,?,?,?)");

			pstmt.setInt(1, id);
			pstmt.setString(2, name);
			pstmt.setString(3, desc);
			pstmt.setString(4, lastedit);
			pstmt.setInt(5, ownerid);
			pstmt.setString(6, loc);
			pstmt.executeUpdate();
			// inserting owner in permissions table
			pstmt = con.prepareStatement("insert into permissions values (?,?,?)");
			pstmt.setInt(1, id);
			pstmt.setInt(2, ownerid);
			pstmt.setString(3, "owner");
			pstmt.executeUpdate();

			pstmt.close();
			con.commit();
			con.setAutoCommit(true);
			releaseConnection(con);
			return id;
		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}
	//Delete Document - delete entries from permissions and document
	public synchronized  void deleteDoc(int docid) {
		try {
			Connection con = getConnection();

			Statement stmt = con.createStatement();
			stmt.executeUpdate("delete from permissions where docid = "+docid);
			stmt.executeUpdate("delete from document where docid = "+docid);
			stmt.close();
			releaseConnection(con);
		} catch (SQLException e) {
			throw new AssertionError(e);
		}

	}
	// To edit permissions of a collaborator
	public void setPermissions(int docid, int userid, String accesstype){
		try {
			Connection con = getConnection();

			Statement stmt = con.createStatement();
			stmt.executeUpdate("update permissions set accesstype = \'"+accesstype+"\'"+" where userid = "+userid+" and docid = "+docid); 

			stmt.close();
			releaseConnection(con);


		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}

	public String getDocName(int docid){
		
		String docName = null;
		try {
			Connection con = getConnection();

			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select docname from document where docid = " + docid); 
			while (rs.next()) {
				docName = rs.getString("docname");
			}
			stmt.close();
			releaseConnection(con);
			
		} catch (SQLException e) {
			throw new AssertionError(e);
		}
		return docName;
	}


}
